////////////////////////////////////////////////////////////////////////////////
// Copyright 2006 CORE Solutions Limited
// All rights reserved.
// PROPRIETARY AND CONFIDENTIAL INFORMATION.
// Use is subject to license terms.
//
// FUNCTION ID      :   ERROR_MSG_LABEL
// AUTHOR           :   Victor Zheng 
// DATE CREATE      :   2006/01/12
// DESCRIPTION      :   a DAO file for the function, ERROR_MSG_LABEL
//                  :   
//                  :   
//                  :   
//
// MODIFICATION HISTORY:
// DATE         AUTHOR          VERSION NO.         COMMENT
// -----------  --------------  ------------------  -------------------
// 2006/01/12   Victor Zheng    1.0                 source is generated.
//
//
//
////////////////////////////////////////////////////////////////////////////////
 
package com.erp.app.admin.errorMsg.dao;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
 
import com.erp.app.admin.errorMsg.criteria.ErrorMsgLabelSrchCriteria;
import com.erp.app.admin.errorMsg.pk.ErrorMsgLabelPk;
import com.erp.app.admin.errorMsg.vo.ErrorMsgLabelVo;
import com.erp.app.common.ProjectConstants;
import com.erp.framework.exceptions.FindRecordException;
import com.erp.framework.model.criteria.SimpleBaseSrchCriteria;
import com.erp.framework.model.dao.SimplePageEditStyleDao;
import com.erp.framework.model.pk.SimpleBasePk;
import com.erp.framework.model.vo.ChildList;
import com.erp.framework.model.vo.SimpleBaseVo;
 
/**
* <p>A <strong>DAO</strong>(Data Access Object) abstracts and encapsulates
* all access to the persistent store.  The DAO manages the connection with the
* data source to obtain and store data.  Also, the DAO implements
* the access mechanism required to work with the data source,
* and always provides a uniform API to its clients.
*
* @author          Victor Zheng 
* @version 1.0     2006/01/12
* @since           FrameWork 1.0
*
*/
public class ErrorMsgLabelDao extends SimplePageEditStyleDao {
 
 /**
 * Returns the SQL select statement.
 * @return        a string containing sql query
 *
 * @author     Victor Zheng 
 * @since      FrameWork 1.0
 */
 protected String sqlSelectByPrimaryKey(){
 
  return sqlFind();
 }
 
 /**
 * Sets the values of a result set to a pk
 * @param         pStmt   the incoming prepared statement
 * @param         pk      the searching primary key
 * @return        a vo containing the result set values
 *
 * @author     Victor Zheng 
 * @since      FrameWork 1.0
 */
 protected void setValuesForSelectByPrimaryKey(PreparedStatement pStmt, SimpleBasePk pk) throws SQLException{
 
  setValuesForFind(pStmt, pk);
 }
 
 /**
 * Returns the constructed model from a result set.
 * @param         rs     the incoming result set
 * @return        a constructed VO from the result set
 *
 * @author     Victor Zheng 
 * @since      FrameWork 1.0
 */
 protected SimpleBaseVo getModelForSelectByPrimarKey(ResultSet rs) throws SQLException {
 
  return getResultSetValues(rs);
 }
 
    
 
 /**
 * Returns the SQL select statement.
 * @param         criteria     the searching criteria
 * @return        a string containing sql query
 *
 * @author     Victor Zheng 
 * @since      FrameWork 1.0
 */
 protected String sqlSelectByCriteria(SimpleBaseSrchCriteria criteria) {
  return sqlQuery(criteria);
    }
    
 /**
 * Returns the constructed model from a result set.
 * @param         rs     the incoming result set
 * @return        a constructed VO from the result set
 *
 * @author     Victor Zheng 
 * @since      FrameWork 1.0
 */
    protected SimpleBaseVo getModelForSelectByCriteria(ResultSet rs) throws SQLException {
     return getResultSetValues(rs);
    }
 
    /**
 * Returns the constructed primary key from a result set.
 * @param         rs     the incoming result set
 * @return        a constructed PK from the result set
 *
 * @author     Victor Zheng 
 * @since      FrameWork 1.0
 */
    protected SimpleBasePk getPkForSelectByCriteria(ResultSet rs) throws SQLException {
     ErrorMsgLabelPk pk = new ErrorMsgLabelPk(rs.getString("COMPANY_CODE"),
       rs.getString("GROUP_ID"), rs.getString("MENU_ID"), rs.getString("OBJECT_ID"),
       rs.getString("LANG_CODE"));
  
  return pk;
    }
    
 
 /**
 * Returns the SQL select statement.
 * @param         obj     the searching criteria
 * @return        a string containing sql query
 *
 * @author     Victor Zheng 
 * @since      FrameWork 1.0
 */
 protected String sqlQuery(SimpleBaseSrchCriteria obj) {
 
  ErrorMsgLabelSrchCriteria criteria = (ErrorMsgLabelSrchCriteria) obj;
  String sql = "";
  String whereClause = toWhereClause(criteria);
  String groupByClause = "";
  String orderByClause = "ORDER BY COMPANY_CODE, GROUP_ID, MENU_ID, OBJECT_ID, LANG_CODE";
 
  if(!criteria.isSearchResult()){
   sql = "SELECT COMPANY_CODE, GROUP_ID, MENU_ID, OBJECT_ID, LANG_CODE, "
   + "OBJECT_NAME, OBJECT_TYPE, OBJECT_WEB_LABEL, CREATE_USER, CREATE_DATE, "
   + "UPDATE_USER, UPDATE_DATE "
   + "FROM T_ERROR_MSG_LABEL "
   + whereClause
   + groupByClause
   + orderByClause;
  } else {
   sql = "SELECT COMPANY_CODE, GROUP_ID, MENU_ID, OBJECT_ID, LANG_CODE, "
    + "OBJECT_NAME, OBJECT_TYPE, OBJECT_WEB_LABEL, CREATE_USER, CREATE_DATE, "
    + "UPDATE_USER, UPDATE_DATE "
    + "FROM T_ERROR_MSG_LABEL "
    + whereClause
    + groupByClause
    + orderByClause;
  }
  //super.logSqlQuery(sql);
  return sql;
 }
 
 /**
 * Returns the where clause of a SQL select statement.
 * @param         obj     the searching criteria
 * @return        a string containing the where clause
 *
 * @author     Victor Zheng 
 * @since      FrameWork 1.0
 */
 protected String toWhereClause(ErrorMsgLabelSrchCriteria crit) {
 
  ErrorMsgLabelSrchCriteria criteria = (ErrorMsgLabelSrchCriteria) crit;
  StringBuffer whereClause = new StringBuffer();
 
  if (criteria.getSrchCompanyCode() != null) {
   whereClause.append(whereClause.length()== 0? "WHERE ": "AND ");
   whereClause.append(" COMPANY_CODE = '" + criteria.getSrchCompanyCode().trim().toUpperCase()+"' ");
  }
 
  if (criteria.getSrchGroupIdFr() != null) {
   whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
   whereClause.append("GROUP_ID >= '"
    + criteria.getSrchGroupIdFr().toUpperCase().trim() + "' ");
  }
  if (criteria.getSrchGroupIdTo() != null) {
   whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
   whereClause.append("GROUP_ID <= '"
    + criteria.getSrchGroupIdTo().toUpperCase().trim() + "' ");
  }
  
  if (criteria.getSrchMenuIdFr() != null) {
   whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
   whereClause.append("MENU_ID >= '"
    + criteria.getSrchMenuIdFr().toUpperCase().trim() + "' ");
  }
  if (criteria.getSrchMenuIdTo() != null) {
   whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
   whereClause.append("MENU_ID <= '"
    + criteria.getSrchMenuIdTo().toUpperCase().trim() + "' ");
  }
  
  if (criteria.getSrchObjectIdFr() != null) {
   whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
   whereClause.append("OBJECT_ID >= '"
    + criteria.getSrchObjectIdFr().toUpperCase().trim() + "' ");
  }
  if (criteria.getSrchObjectIdTo() != null) {
   whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
   whereClause.append("OBJECT_ID <= '"
    + criteria.getSrchObjectIdTo().toUpperCase().trim() + "' ");
  }
  
  if (criteria.getSrchLangCodeFr() != null) {
   whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
   whereClause.append("LANG_CODE >= '"
    + criteria.getSrchLangCodeFr().trim() + "' ");
  }
  if (criteria.getSrchLangCodeTo() != null) {
   whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
   whereClause.append("LANG_CODE <= '"
    + criteria.getSrchLangCodeTo().trim() + "' ");
  }
  
  if (criteria.getSrchObjectNameFr() != null) {
   whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
   whereClause.append("OBJECT_NAME >= '"
    + criteria.getSrchObjectNameFr().toUpperCase().trim() + "' ");
  }
  if (criteria.getSrchObjectNameTo() != null) {
   whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
   whereClause.append("OBJECT_NAME <= '"
    + criteria.getSrchObjectNameTo().toUpperCase().trim() + "' ");
  }
  
  if (criteria.getSrchObjectTypeFr() != null) {
   whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
   whereClause.append("OBJECT_TYPE >= '"
    + criteria.getSrchObjectTypeFr().trim() + "' ");
  }
  if (criteria.getSrchObjectTypeTo() != null) {
   whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
   whereClause.append("OBJECT_TYPE <= '"
    + criteria.getSrchObjectTypeTo().trim() + "' ");
  }
  
  if (criteria.getSrchObjectWebLabelFr() != null) {
   whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
   whereClause.append("OBJECT_WEB_LABEL >= '"
    + criteria.getSrchObjectWebLabelFr().toUpperCase().trim() + "' ");
  }
  if (criteria.getSrchObjectWebLabelTo() != null) {
   whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
   whereClause.append("OBJECT_WEB_LABEL <= '"
    + criteria.getSrchObjectWebLabelTo().toUpperCase().trim() + "' ");
  }
  
  
  /*
  if (criteria.getSrchProgram() != null) {
   whereClause.append(whereClause.length() == 0 ? " WHERE " : " AND ");
   whereClause.append(" SALES_CONTRACT_NO IN ");
   whereClause.append(" (SELECT SALES_CONTRACT_NO FROM T_SALES_CONTRACT_DTL");
   if (criteria.getSrchProgram() != null) {
    whereClause.append(" WHERE PROGRAM ='" + criteria.getSrchProgram().toUpperCase().trim() + "'");
   }
   whereClause.append(")");
  }
  if (criteria.getSrchSalesContractNoFr() != null) {
   whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
   whereClause.append("SALES_CONTRACT_NO >= '"
    + criteria.getSrchSalesContractNoFr().toUpperCase().trim() + "' ");
  }
  if (criteria.getSrchSalesContractNoTo() != null) {
   whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
   whereClause.append("SALES_CONTRACT_NO <= '"
    + criteria.getSrchSalesContractNoTo().toUpperCase().trim() + "' ");
  }
  if (criteria.getSrchPoDateFr() != null) {
   whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
   whereClause.append(" PO_DATE >= TO_DATE('"
    + criteria.getSrchPoDateFr() + "', '"
    + ProjectConstants.ORACLE_INPUT_DATE_FORMAT + "') ");
  }
  if (criteria.getSrchPoDateTo() != null) {
   whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
   whereClause.append(" PO_DATE <= TO_DATE('"
    + criteria.getSrchPoDateTo() + "', '"
    + ProjectConstants.ORACLE_INPUT_DATE_FORMAT + "') ");
  }
  */
 
  return whereClause.toString();
 }
 
 /**
 * Returns an array list of child VOs by the parent
 * @param         parent   the incoming parent VO
 * @param         conn     the database connection
 * @return        an array list of child VOs
 *
 * @author     Victor Zheng 
 * @since      FrameWork 1.0
 */
 public ChildList selectByParent(SimpleBaseVo parent, Connection conn)
  throws FindRecordException {
 
  return null;
 }
 
 /**
 * Constructs a string of SQL insert statement
 * @return        a string of SQL insert statement
 *
 * @author     Victor Zheng 
 * @since      FrameWork 1.0
 */
 protected String sqlInsert() {
 
  String sql = "INSERT INTO T_ERROR_MSG_LABEL ("
   + "COMPANY_CODE, GROUP_ID, MENU_ID, OBJECT_ID, LANG_CODE, "
   + "OBJECT_NAME, OBJECT_TYPE, OBJECT_WEB_LABEL, CREATE_USER, CREATE_DATE, "
   + "UPDATE_USER, UPDATE_DATE) "
   + "VALUES ("
   + "?, ?, ?, ?, ?, "
   + "?, ?, ?, ?, SYSDATE, "
   + "?, ?)";
 
  //super.logSqlQuery(sql);
  return sql;
 }
 
 /**
 * Sets the values for a SQL insert or update statement.
 * @param         pStmt   the incoming prepared statement
 * @param         obj     the incoming VO
 *
 * @author     Victor Zheng 
 * @since      FrameWork 1.0
 */
 protected void setValuesForInsertAndModify(PreparedStatement pStmt, Object obj, String opType)
  throws SQLException {
 
  ErrorMsgLabelVo vo = (ErrorMsgLabelVo) obj;
  int i = 1;
 
  pStmt.setString(i++, vo.getCompanyCode());
  pStmt.setString(i++, vo.getGroupId());
  pStmt.setString(i++, vo.getMenuId());
  pStmt.setString(i++, vo.getObjectId());
  pStmt.setString(i++, vo.getLangCode());
  setNullableString(pStmt, i++, vo.getObjectName());
  setNullableString(pStmt, i++, vo.getObjectType());
  setNullableString(pStmt, i++, vo.getObjectWebLabel());
  setNullableString(pStmt, i++, vo.getCreateUser());
 
  if (ProjectConstants.DB_OP_UPDATE.equals(opType))
   setNullableTimestamp(pStmt, i++, vo.getCreateDate());
 
  setNullableString(pStmt, i++, vo.getUpdateUser());
 
  if (ProjectConstants.DB_OP_INSERT.equals(opType))
   setNullableTimestamp(pStmt, i++, vo.getUpdateDate());
 
  if(ProjectConstants.DB_OP_UPDATE.equals(opType)){
   pStmt.setString(i++, vo.getCompanyCode());
   pStmt.setString(i++, vo.getGroupId());
   pStmt.setString(i++, vo.getMenuId());
   pStmt.setString(i++, vo.getObjectId());
   pStmt.setString(i++, vo.getLangCode());
   setNullableTimestamp(pStmt, i++, vo.getUpdateDate());
  }
 }
 
 /**
 * Sets the values for a SQL insert statement.
 * @param         pStmt   the incoming prepared statement
 * @param         obj     the incoming VO
 *
 * @author     Victor Zheng 
 * @since      FrameWork 1.0
 */
 protected void setValuesForInsert(PreparedStatement pStmt, Object obj)
  throws SQLException {
 
  setValuesForInsertAndModify(pStmt, obj, ProjectConstants.DB_OP_INSERT);
 }
 
 /**
 * Constructs a string of SQL update statement
 * @return        a string of SQL update statement
 *
 * @author     Victor Zheng 
 * @since      FrameWork 1.0
 */
 protected String sqlUpdate() {
 
  String sql = "UPDATE T_ERROR_MSG_LABEL SET COMPANY_CODE=?, GROUP_ID=?, MENU_ID=?, OBJECT_ID=?, "
   + "LANG_CODE=?, OBJECT_NAME=?, OBJECT_TYPE=?, OBJECT_WEB_LABEL=?, CREATE_USER=?, CREATE_DATE=?, "
   + "UPDATE_USER=?, UPDATE_DATE=SYSDATE "
   + "WHERE COMPANY_CODE=? AND GROUP_ID=? AND MENU_ID=? AND OBJECT_ID=? AND "
   + "LANG_CODE=? "
   + "AND (UPDATE_DATE IS NULL OR UPDATE_DATE = ?) ";
 
  //super.logSqlQuery(sql);
  return sql;
 }
 
 /**
 * Sets the values for a SQL update statement.
 * @param         pStmt   the incoming prepared statement
 * @param         obj     the incoming VO
 *
 * @author     Victor Zheng 
 * @since      FrameWork 1.0
 */
 protected void setValuesForModify(PreparedStatement pStmt, Object obj)
  throws SQLException {
 
  setValuesForInsertAndModify(pStmt, obj, ProjectConstants.DB_OP_UPDATE);
 }
 
 /**
 * Constructs a string of SQL delete statement
 * @return        a string of SQL update statement
 *
 * @author     Victor Zheng 
 * @since      FrameWork 1.0
 */
 protected String sqlDelete() {
  String sql = "DELETE FROM T_ERROR_MSG_LABEL WHERE COMPANY_CODE=? AND GROUP_ID=? AND MENU_ID=? AND OBJECT_ID=? AND "
   + "LANG_CODE=? ";
 
  //super.logSqlQuery(sql);
  return sql;
 }
 
 /**
 * Sets the values for a SQL delete statement.
 * @param         pStmt   the incoming prepared statement
 * @param         obj     the incoming VO
 *
 * @author     Victor Zheng 
 * @since      FrameWork 1.0
 */
 protected void setValuesForDelete(PreparedStatement pStmt, Object obj)
  throws SQLException {
 
  ErrorMsgLabelVo vo = (ErrorMsgLabelVo) obj;
  int i = 1;
 
  pStmt.setString(i++, vo.getCompanyCode());
  pStmt.setString(i++, vo.getGroupId());
  pStmt.setString(i++, vo.getMenuId());
  pStmt.setString(i++, vo.getObjectId());
  pStmt.setString(i++, vo.getLangCode());
 }
 
 /**
 * Constructs a string of SQL select statement
 * @return        a string of SQL select statement
 *
 * @author     Victor Zheng 
 * @since      FrameWork 1.0
 */
 protected String sqlFind() {
 
  String sql = "SELECT COMPANY_CODE, GROUP_ID, MENU_ID, OBJECT_ID, LANG_CODE, "
   + "OBJECT_NAME, OBJECT_TYPE, OBJECT_WEB_LABEL, CREATE_USER, CREATE_DATE, "
   + "UPDATE_USER, UPDATE_DATE "
   + "FROM T_ERROR_MSG_LABEL "
   + "WHERE COMPANY_CODE=? AND GROUP_ID=? AND MENU_ID=? AND OBJECT_ID=? AND "
   + "LANG_CODE=? "
   + "ORDER BY COMPANY_CODE, GROUP_ID, MENU_ID, OBJECT_ID, LANG_CODE";
 
  //super.logSqlQuery(sql);
  return sql;
 }
 
 /**
 * Sets the values for a SQL select statement for finding by primary key.
 * @param         pStmt   the incoming prepared statement
 * @param         obj     the incoming PK
 *
 * @author     Victor Zheng 
 * @since      FrameWork 1.0
 */
 protected void setValuesForFind(PreparedStatement pStmt, Object obj)
  throws SQLException {
 
  ErrorMsgLabelPk pk = (ErrorMsgLabelPk) obj;
  int i = 1;
 
  pStmt.setString(i++, pk.getCompanyCode());
  pStmt.setString(i++, pk.getGroupId());
  pStmt.setString(i++, pk.getMenuId());
  pStmt.setString(i++, pk.getObjectId());
  pStmt.setString(i++, pk.getLangCode());
 }
 
 /**
 * Constructs a string of SQL select statement
 * @return        a string of SQL select statement
 *
 * @author     Victor Zheng 
 * @since      FrameWork 1.0
 */
 protected String sqlList() {
 
  return null;
 }
 
 /**
 * Sets the values for a SQL select statement for finding child tables.
 * @param         pStmt   the incoming prepared statement
 * @param         obj     the incoming parent PK
 *
 * @author     Victor Zheng 
 * @since      FrameWork 1.0
 */
 protected void setValuesForList(PreparedStatement pStmt, Object obj)
  throws SQLException {
 
 }
 
 /**
 * Sets the values of a result set to a vo for a search screen
 * @param         rs   the incoming result set
 * @return        a vo containing the result set values
 *
 * @author     Victor Zheng 
 * @since      FrameWork 1.0
 */
 protected SimpleBaseVo getSearchResultSetValues(ResultSet rs) throws SQLException {
  return getResultSetValues(rs);
 }
 
 /**
 * Sets the values of a result set to a vo
 * @param         rs   the incoming result set
 * @return        a vo containing the result set values
 *
 * @author     Victor Zheng 
 * @since      FrameWork 1.0
 */
 protected SimpleBaseVo getResultSetValues(ResultSet rs) throws SQLException {
 
  ErrorMsgLabelVo vo = new ErrorMsgLabelVo();
 
  vo.setCompanyCode(rs.getString("COMPANY_CODE"));
  vo.setGroupId(rs.getString("GROUP_ID"));
  vo.setMenuId(rs.getString("MENU_ID"));
  vo.setObjectId(rs.getString("OBJECT_ID"));
  vo.setLangCode(rs.getString("LANG_CODE"));
  vo.setObjectName(rs.getString("OBJECT_NAME"));
  vo.setObjectType(rs.getString("OBJECT_TYPE"));
  vo.setObjectWebLabel(rs.getString("OBJECT_WEB_LABEL"));
  vo.setCreateUser(rs.getString("CREATE_USER"));
  vo.setCreateDate(rs.getTimestamp("CREATE_DATE"));
  vo.setUpdateUser(rs.getString("UPDATE_USER"));
  vo.setUpdateDate(rs.getTimestamp("UPDATE_DATE"));
 
  return vo;
 }
 
}
